package com.ck.ips.service.impl;

import com.ck.common.model.DeviceSetting;
import com.ck.common.model.DeviceStore;
import com.ck.common.model.OperationResult;
import com.ck.common.model.Sysconfig;
import com.ck.common.model.bo.DeviceStateChangeBO;
import com.ck.common.page.PageInfo;
import com.ck.core.cache.redis.RedisCacheDao;
import com.ck.core.constant.CacheConstant;
import com.ck.core.constant.PageConstant;
import com.ck.core.constant.SymbolConstant;
import com.ck.core.constant.SysConfigNameEnum;
import com.ck.core.constant.device.DeviceSettingType;
import com.ck.ips.dao.DeviceMapper;
import com.ck.ips.dao.DeviceSettingMapper;
import com.ck.ips.dao.store.StoreMapper;
import com.ck.ips.model.vo.DeviceProgramCountdownVO;
import com.ck.ips.model.vo.device.DeviceBasicInfoVO;
import com.ck.ips.model.vo.device.DeviceDetailVO;
import com.ck.ips.model.vo.device.DeviceGroupCountVO;
import com.ck.ips.model.vo.program.DeviceAddAndReducedStatisticVO;
import com.ck.ips.model.vo.program.ProgramPlayListVO;
import com.ck.ips.service.DeviceGroupService;
import com.ck.ips.service.DeviceService;
import com.ck.ips.service.program.ProgramService;
import com.ck.ips.service.sys.SysConfigService;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.text.Collator;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
* @Description: 设备serviceImpl
* @Author: hf
* @Date:    2019/5/6 17:35
* @Version: 1.0
*/
@Service(value="deviceService")
public class DeviceServiceImpl implements DeviceService {

    private Logger logger = LoggerFactory.getLogger(DeviceServiceImpl.class);
    @Autowired
    private DeviceMapper deviceMapper;
    @Autowired
    private DeviceSettingMapper deviceSettingMapper;
    @Autowired
    private ProgramService programService;
    @Autowired
    private RedisCacheDao redisCacheDao;
    @Autowired
    private StoreMapper storeMapper;
    @Autowired
    private DeviceGroupService deviceGroupService;
    @Autowired
    private SysConfigService sysConfigService;

    @Override
    public List<String> getDistinctResolvingPowerByDeviceCodes(List<String> deviceCodes, List<String> deviceGroupIdList) {
        return deviceMapper.getDistinctResolvingPowerByDeviceCodes(deviceCodes, deviceGroupIdList);
    }

    @Override
    public PageInfo<DeviceBasicInfoVO> listBasicDeviceInfoByPage(Integer pageNum, Integer pageSize
            , String groupId, String deviceName, String deviceMac, String ip
            , String deviceStatus, String deviceSerialNum, String deviceType, String resolvingPower
            , Integer bindingStatus, String screenSize, Integer belowSoftVersion, String groupName, String storeName, Boolean programDue, List<String> queryDeviceCodeList) {
        pageNum = (pageNum == null ? PageConstant.DEFAULT_PAGE_NUM : pageNum);
        pageSize = (pageSize == null ? PageConstant.DEFAULT_PAGE_SIZE : pageSize);

        // 分组ID（拼接多个）
        List<String> groupIds = null;
        if ( StringUtils.isNotBlank(groupId) ) {
            groupIds = Arrays.asList(groupId.split(SymbolConstant.COMMA_EN));
        }

//        // 非超管用户需要验证数据权限
//        if ( !UserContextHolder.currentUser().isSuper() ) {
//            List<String> userDeviceGroupId = deviceGroupService.findUserDeviceGroupId();
//            if ( userDeviceGroupId == null || userDeviceGroupId.isEmpty() )  {
//                return PageInfo.init();
//            }
//
//            if ( groupIds != null ) {
//                if ( !userDeviceGroupId.containsAll(groupIds) ) {
//                    // 数据越权 -- 》
//                    return PageInfo.init();
//                }
//            } else {
//                // 将当前用户拥有的可操作分组id当作查询条件
//                groupIds = userDeviceGroupId;
//            }
//        }

        // 分辨率(拼接多个)
        List<String> resolvingPowers = new ArrayList<>();
        if ( StringUtils.isNotBlank(resolvingPower) ) {
            resolvingPowers = Arrays.asList(resolvingPower.split(SymbolConstant.COMMA_EN));
        }

        PageHelper.startPage(pageNum, pageSize);
        Page<DeviceBasicInfoVO> pageResult = deviceMapper.listBasicDeviceInfoByPage(null, groupIds,  deviceName, deviceMac, ip, deviceStatus,  deviceSerialNum,  deviceType, resolvingPowers, bindingStatus,screenSize,belowSoftVersion,groupName,storeName, queryDeviceCodeList);
        PageInfo pageInfo = PageInfo.convert(pageResult);
        if ( CollectionUtils.isNotEmpty(pageInfo.getList()) ) {

            // TODO 空刊状态设置，由于前期业务涉及原因，这里只能通过查询完分页后在手动设置。由于现在前端默认是不分页的查询方式，因此可以这样设置，如果之后查询需要分页，则需要业务改动
            List<DeviceBasicInfoVO> list = setDeviceProgramCountdown(pageInfo.getList(), programDue);
            // 根据名称排序
            list = sort(list);//ListSortUtils.sortDeviceListByGroupNameAndDeviceName(list);
            if (CollectionUtils.isEmpty(list)){
                pageInfo.setList(list);
                pageInfo.setTotal(0);
                return pageInfo;
            }
            pageInfo.setList(list);
            pageInfo.setTotal(list.size());
            List<String> deviceCodeList = new ArrayList<>();
            for ( DeviceBasicInfoVO ele : list ){
                deviceCodeList.add(ele.getDeviceCode());
            }

            // 设置增播、减播数量
            List<DeviceAddAndReducedStatisticVO> addedAndReducedStatisticInfoByDeviceCodes = programService.getAddedAndReducedStatisticInfoByDeviceCodes(deviceCodeList);
            if ( addedAndReducedStatisticInfoByDeviceCodes != null ) {
                for ( DeviceBasicInfoVO ele : list ){
                    for ( DeviceAddAndReducedStatisticVO deviceAddAndReducedStatisticVO : addedAndReducedStatisticInfoByDeviceCodes ) {
                        if ( ele.getDeviceCode().equals(deviceAddAndReducedStatisticVO.getDeviceCode()) ) {
                            ele.setAddProgramsCount(deviceAddAndReducedStatisticVO.getAddedCount());
                            ele.setReduceProgramsCount(deviceAddAndReducedStatisticVO.getReducedCount());
                        }
                    }
                }
            }
        }
        return pageInfo;
    }

    private List<DeviceBasicInfoVO> sort(List<DeviceBasicInfoVO> list) {
        List<DeviceBasicInfoVO> sortedList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(list)){
            Map<String, List<DeviceBasicInfoVO>> collect = list.stream().collect(Collectors.groupingBy(DeviceBasicInfoVO::getGroupName));
            Set<String> allKeys = collect.keySet();
            // 分组名按中文排序
            Set<String> sortedKeys = new TreeSet<>(Collator.getInstance(Locale.CHINESE));
            sortedKeys.addAll(allKeys);
            // 分组内的设备按设备名自然排序
            for (String key : sortedKeys){
                List<DeviceBasicInfoVO> deviceNameSortedList = collect.get(key);

                Collections.sort(deviceNameSortedList, (info1, info2) -> {
                      if (info1.getDeviceName() != null && info2.getDeviceName() != null) {
                          //获取中文环境
                          Comparator<Object> com = Collator.getInstance(Locale.CHINESE);
                          return com.compare(info1.getDeviceName(), info2.getDeviceName());
                      }
                      return info1.getDeviceName() == null ? -1 : 1;
                });
                sortedList.addAll(deviceNameSortedList);
            }
        }
        return sortedList;
    }

    private List<DeviceBasicInfoVO> setDeviceProgramCountdown(List<DeviceBasicInfoVO> result, Boolean programDue) {

        // 空刊判定时间
        Long countdownTime = System.currentTimeMillis();

        // 获取空刊时间判定的系统配置值
        Sysconfig config = sysConfigService.getByType(SysConfigNameEnum.DEVICE_NO_PROGRAM_COUNTDOWN_TIME);
        if (config != null) {
            countdownTime = countdownTime + Long.valueOf(config.getValue())*60*60*1000;
        }

        // 获取当前时刻所有设备的最大下刊时间
        List<DeviceProgramCountdownVO> deviceProgramCountdownList = deviceMapper.getDeviceProgramCountdown();
        if (CollectionUtils.isEmpty(deviceProgramCountdownList)){
            return result;
        }

        // 双重循环遍历，设置空刊状态
        for (DeviceBasicInfoVO deviceBasicInfoVO : result){
            for (DeviceProgramCountdownVO deviceProgramCountdownVO : deviceProgramCountdownList) {
                if (deviceBasicInfoVO.getDeviceCode().equals(deviceProgramCountdownVO.getDeviceCode()) && deviceProgramCountdownVO.getCountdownTime() > countdownTime){
                    deviceBasicInfoVO.setProgramDue(Boolean.FALSE);
                }
            }
        }
        if (programDue != null && Boolean.TRUE.equals(programDue)){
            result = result.stream().filter(e -> Boolean.TRUE.equals(e.getProgramDue())).collect(Collectors.toList());
        }
        if (programDue != null && Boolean.FALSE.equals(programDue)){
            result = result.stream().filter(e -> Boolean.FALSE.equals(e.getProgramDue())).collect(Collectors.toList());
        }
        return result;
    }

    @Override
    public DeviceDetailVO getDetailByDeviceCode(String deviceCode) {
        return deviceMapper.getDetailByDeviceCode( deviceCode );
    }

    /**
     * 根据节目版本号查询设备序列号
     * @param programVersionList 节目版本号集合
     * @return 设备序列号集合
     */
    @Override
    public List<String> listDeviceCodeByProgramVersion(List<String> programVersionList) {
        return deviceMapper.listDeviceCodeByProgramVersion(programVersionList);
    }

    /**
     * 刷新设备设置缓存
     * @param deviceSettingType 设备类型
     * @param codeList 设备序列号
     * @param serialNo 批次号
     */
    @Override
    public void flushDeviceStateCache(DeviceSettingType deviceSettingType, List<String> codeList, Long serialNo) {
        // 将更新存储到缓存的业务，不应受事务控制（即：及时缓存更新失败，或操作过程抛出异常也应当保证数据的正常入库操作）
        try {
            List<String> codesClone = new ArrayList<>(codeList);
            for (Iterator<String> it = codesClone.iterator(); it.hasNext(); ) {
                String deviceCode = it.next();

                // 设备操作
                DeviceStateChangeBO deviceStateBO = (DeviceStateChangeBO) redisCacheDao.getCache(CacheConstant.CACHE_NAME_DEVICE_SETTING, deviceCode);
                if (deviceStateBO == null) {
                    deviceStateBO = new DeviceStateChangeBO();
                }
                switch (deviceSettingType) {
                    case SHUT_DOWN: // 关机
                    case RESTART: // 重启
                    case BOOT_OFF_TIMER: // 定时开关机
                    case SCREEN_TIMER: // 定时开关屏
                    case SET_VOLUME: // 设置音量
                    case WIFI_SWITCH: // wifi探针开关
                    case FACE_SWITCH: // 人脸识别开关
                        deviceStateBO.setNewSetting(true);
                        deviceStateBO.setNewSettingUpdateTime(serialNo);
                        break;
                    case SOFTWARE_UPGRADE: // 软件升级
                    case FIRMWARE_UPGRADE: // 固件升级
                        deviceStateBO.setNewUpgrade(true);
                        deviceStateBO.setNewUpgradeUpdateTime(serialNo);
                        break;
                    case PROGRAM_UPDATE: // 节目信息更新
                        deviceStateBO.setNewProgram(true);
                        deviceStateBO.setNewProgramUpdateTime(serialNo);
                        break;
                    default:
                        break;
                }
                redisCacheDao.putCache(CacheConstant.CACHE_NAME_DEVICE_SETTING, deviceCode, deviceStateBO);
            }
        } catch (Exception e) {
            logger.error("flushDeviceStateCache 刷新缓存业务异常：" + e.getMessage(), e);
        }
    }

    @Override
    public void changeDeviceGroupToAnother(Long deviceGroupId, Long anotherDeviceGroupId) {
        deviceMapper.changeDeviceGroupToAnother(deviceGroupId,anotherDeviceGroupId);
    }

    @Override
    public OperationResult updateDeviceDetail(DeviceStore deviceStore) {
        if ( StringUtils.isBlank(deviceStore.getDeviceCode()) ) {
            return OperationResult.newValidateFailOperationResult("终端唯一编码不能为空");
        }
        deviceMapper.updateDeviceDetail(deviceStore);
        return OperationResult.newSuccessTipsOperationResult("成功修改设备信息：" + deviceStore.getDeviceCode());
    }

    @Override
    @Transactional( rollbackFor = Exception.class )
    public void updateDeviceSetting(DeviceSetting setting, List<String> deviceCodes) {
        deviceSettingMapper.updateDeviceSetting(setting, deviceCodes);
    }

    @Override
    public OperationResult shutdown(String deviceCodes) {
        DeviceSetting setting = new DeviceSetting();
        setting.setShutDownOnce(Boolean.TRUE);
        return setting(DeviceSettingType.SHUT_DOWN, deviceCodes, setting);
    }

    @Override
    public OperationResult restart(String deviceCodes) {
        DeviceSetting setting = new DeviceSetting();
        setting.setRestartOnce(Boolean.TRUE);
        return  setting(DeviceSettingType.RESTART, deviceCodes, setting);
    }

    @Override
    public OperationResult setTimerShutDownOrStart(String deviceCodes,String bootTime,String offTime
                                                   ,String nonWorkDayBootTime,String nonWorkDayOffTime) {
        DeviceSetting setting = new DeviceSetting();
        setting.setTimeBoot(bootTime);
        setting.setTimeShutdown(offTime);
        setting.setNonWorkDayTimeBoot(nonWorkDayBootTime);
        setting.setNonWorkDayTimeShutdown(nonWorkDayOffTime);
        return setting(DeviceSettingType.BOOT_OFF_TIMER, deviceCodes, setting);
    }

    @Override
    public OperationResult wifiProbeSwitch(String deviceCodes,Integer status) {
        DeviceSetting setting = new DeviceSetting();
        setting.setWifiSwitch(status);
        return setting(DeviceSettingType.WIFI_SWITCH, deviceCodes, setting);
    }

    @Override
    public OperationResult faceRecognitionSwitch(String deviceCodes,Integer status) {
        DeviceSetting setting = new DeviceSetting();
        setting.setFaceSwitch(status);
        return setting(DeviceSettingType.FACE_SWITCH, deviceCodes, setting);
    }

    @Override
    public OperationResult setScreenTimerShutDownOrStart(String deviceCodes,String bootTime, String offTime) {
        DeviceSetting setting = new DeviceSetting();
        setting.setTimeBootScreen(bootTime);
        setting.setTimeShutdownScreen(offTime);
        return setting(DeviceSettingType.SCREEN_TIMER, deviceCodes, setting);
    }

    @Override
    public OperationResult setVolume(String deviceCodes,Integer vol) {
        DeviceSetting setting = new DeviceSetting();
        setting.setDeviceVolume(vol);
        return setting(DeviceSettingType.SET_VOLUME, deviceCodes, setting);
    }

    @Override
    public OperationResult untie(String deviceCodes) {
        if ( StringUtils.isBlank(deviceCodes) ) {
            return OperationResult.newValidateFailOperationResult("解绑终端信息不能为空");
        }

        List<String> deviceCodeList = Arrays.asList(deviceCodes.split(SymbolConstant.COMMA_EN));

        // 1、删除节目相关数据
        programService.batchDeleteByDeviceCodeForDeviceUnit(deviceCodeList);

        // 2、更改device表的绑定状态
        Long lastUntieBindingTime = System.currentTimeMillis();
        deviceMapper.untie(deviceCodeList,lastUntieBindingTime);

        // 3、删除device_store表的数据,删除后需要重新导入
        storeMapper.deleteStoreDevice(deviceCodeList);

        // 解绑后删除设备关键缓存信息
        for (String code : deviceCodeList) {
            redisCacheDao.deleteByKey(CacheConstant.CACHE_NAME_DEVICE_KEY_MSG, code);
        }
        return OperationResult.newSuccessTipsOperationResult("解绑终端："+deviceCodes);
    }

    @Override
    public List<String> getDistinctTypeByDeviceCodes(List<String> deviceCodeList) {
        return null;
    }

    @Override
    public List<DeviceGroupCountVO> getDeviceCountByDeviceGroup() {
        return deviceMapper.getDeviceCountByDeviceGroup();
    }

    @Override
    public XSSFWorkbook exportDeviceExcel(Integer pageNum, Integer pageSize, String groupId, String deviceName, String deviceMac, String ip, String deviceStatus, String deviceSerialNum, String deviceType, String resolvingPower, Integer bindingStatus, String screenSize, Integer belowSoftVersion, String groupName, String storeName, Boolean programDue) {
        PageInfo<DeviceBasicInfoVO> deviceList = this.listBasicDeviceInfoByPage(pageNum, pageSize, groupId, deviceName, deviceMac, ip, deviceStatus, deviceSerialNum, deviceType, resolvingPower, bindingStatus, screenSize, belowSoftVersion, groupName, storeName, programDue, null);
        List<DeviceBasicInfoVO> list = deviceList.getList();
        //构造excel并赋值
        //表头数据
        String[] header = {"终端组", "设备IP", "设备MAC", "终端名", "在线状态","播放状态","设备类型/尺寸","分辨率","导入门店","终端任务","Mips版本"};
        //声明一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //生成一个表格，设置表格名称
        XSSFSheet sheet = workbook.createSheet("设备列表");
        //设置表格列宽度为20个字节
        sheet.setDefaultColumnWidth(30);
        //创建第一行表头
        XSSFRow headrow = sheet.createRow(0);
        //遍历添加表头
        for (int i = 0; i < header.length; i++) {
            //创建一个单元格
            XSSFCell cell = headrow.createCell(i);
            //创建一个内容对象
            XSSFRichTextString text = new XSSFRichTextString(header[i]);
            //将内容对象的文字内容写入到单元格中
            cell.setCellValue(text);
        }
        //向excel表中装入数据
        Integer i=0;
        for(DeviceBasicInfoVO device : list){
            i++;
            //建一行
            XSSFRow headrow1 = sheet.createRow(i);
            //建一个单元格 终端组
            XSSFCell cell = headrow1.createCell(0);
            XSSFRichTextString text = new XSSFRichTextString(device.getGroupName());
            cell.setCellValue(text);
            //建一个单元格 设备ip
            cell = headrow1.createCell(1);
            text = new XSSFRichTextString(device.getLastReportIp());
            cell.setCellValue(text);
            //建一个单元格 设备mac
            cell = headrow1.createCell(2);
            text = new XSSFRichTextString(device.getDeviceMac());
            cell.setCellValue(text);
            //建一个单元格 终端名
            cell = headrow1.createCell(3);
            text = new XSSFRichTextString(device.getDeviceName());
            cell.setCellValue(text);
            //建一个单元格 在线状态
            cell = headrow1.createCell(4);
            text = new XSSFRichTextString(device.getDeviceStatus());
            cell.setCellValue(text);
            //建一个单元格 是否空刊
            cell = headrow1.createCell(5);
            text = new XSSFRichTextString(device.getProgramDue() ? "空刊" : "有刊");
            cell.setCellValue(text);
            //建一个单元格 设备类型/尺寸
            cell = headrow1.createCell(6);
            String type = device.getDeviceType()==null?"":device.getDeviceType();
            String screen = device.getScreenSize()==null?"":device.getScreenSize();
            text = new XSSFRichTextString(type+"/"+screen);
            cell.setCellValue(text);
            //建一个单元格 分辨率
            cell = headrow1.createCell(7);
            text = new XSSFRichTextString(device.getResolvingPower());
            cell.setCellValue(text);
            //建一个单元格 导入门店
            cell = headrow1.createCell(8);
            text = new XSSFRichTextString(device.getStoreName());
            cell.setCellValue(text);
            //建一个单元格 终端任务
            cell = headrow1.createCell(9);
            text = new XSSFRichTextString("(+"+device.getAddProgramsCount().toString()+",-"+device.getReduceProgramsCount().toString()+")");
            cell.setCellValue(text);
            //建一个单元格 Mips版本
            cell = headrow1.createCell(10);
            text = new XSSFRichTextString(device.getSoftwareVersion());
            cell.setCellValue(text);
        }
        return workbook;
    }

    @Override
    public List<String> listTodayNewDeviceCode() {
        //一天的时间戳
        long nowTime =System.currentTimeMillis();
        //今日零点的时间戳
        long todayStartTime = nowTime - (nowTime + TimeZone.getDefault().getRawOffset())% (1000*3600*24);
        return deviceMapper.listTodayNewDeviceCode(todayStartTime);
    }

    private OperationResult setting(DeviceSettingType settingType, String deviceCode, DeviceSetting setting) {
        if ( settingType == null || StringUtils.isBlank(deviceCode) || setting == null ) {
            return OperationResult.newValidateFailOperationResult("修改终端设置参数不能为空");
        }
        String operationMsg = String.format("操作行为：%s. 操作对象：[终端:%s]", settingType.getSettingType(), deviceCode);
        logger.info(operationMsg);
        List<String> deviceCodes = Arrays.asList(deviceCode.split(SymbolConstant.COMMA_EN));

        if (CollectionUtils.isNotEmpty(deviceCodes)) {
            // 记录批次号（时间戳）保证数据库跟缓存中更新时间一致
            Long serialNo = System.currentTimeMillis();

            // 1.设置信息同步到数据库
            setting.setUpdateTime(serialNo);
            updateDeviceSetting(setting, deviceCodes);

            // 3.将最新状态更新到缓存
            flushDeviceStateCache( settingType, deviceCodes, serialNo);
        }
        return OperationResult.newSuccessTipsOperationResult(operationMsg);
    }
    @Override
    public XSSFWorkbook exportDeviceProgramListExcel(Integer pageNum, Integer pageSize, String groupId, String deviceName, String deviceMac, String ip, String deviceStatus, String deviceSerialNum, String deviceType, String resolvingPower, Integer bindingStatus, String screenSize, Integer belowSoftVersion, String groupName, String storeName, Boolean programDue, Long startTime, Long endTime) {
        //获取到设备信息
        PageInfo<DeviceBasicInfoVO> deviceList = this.listBasicDeviceInfoByPage(pageNum, pageSize, groupId, deviceName, deviceMac, ip, deviceStatus, deviceSerialNum, deviceType, resolvingPower, bindingStatus, screenSize, belowSoftVersion, groupName, storeName, programDue,null);
        List<DeviceBasicInfoVO> list = deviceList.getList();
        if (list == null) {
            list = new ArrayList<>();
        }

        //获取所有的展示日期，并进行封装
        if (startTime == null || endTime == null || endTime < startTime) {
            throw new IllegalArgumentException("选择的结束日期小于开始日期");
        }
        List<String> allShowDays = getFormatDaysList(startTime, endTime);
        Map<String, Integer> dayMap = new HashMap<>();
        for (int i = 0; i < allShowDays.size(); i++) {
            String day = allShowDays.get(i);
            dayMap.put(day, i);
        }

        //--------------------------------构造excel并赋值--------------------------------------------------------------
        //声明一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //生成一个表格，设置表格名称
        XSSFSheet sheet = workbook.createSheet("上刊数据");
        //设置表格列宽度为20个字节
        sheet.setDefaultColumnWidth(20);
        //单元格样式
        XSSFCellStyle cellHeaderStyle = workbook.createCellStyle();
        cellHeaderStyle.setAlignment(HorizontalAlignment.CENTER);
        //表头字体
        XSSFFont headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setFontHeightInPoints((short) 16);
        cellHeaderStyle.setFont(headerFont);

        //单元格样式
        XSSFCellStyle cellHeaderStyle2 = workbook.createCellStyle();
        //表头字体
        XSSFFont headerFont2 = workbook.createFont();
        headerFont2.setBold(true);
        headerFont2.setFontHeightInPoints((short) 10);
        cellHeaderStyle2.setFont(headerFont2);

        //------------------------------设置头部信息----------------------------------------
        //表头数据
        String[] header = {"终端组", "终端名称", "终端位置", "设备类型/尺寸", "分辨率"};
        //创建第一行表头
        XSSFRow titleRow = sheet.createRow(0);
        //创建一个单元格
        XSSFCell titleRowCell = titleRow.createCell(0);
        //创建一个内容对象
        XSSFRichTextString titleRowText = new XSSFRichTextString("上刊数据");
        //将内容对象的文字内容写入到单元格中
        titleRowCell.setCellValue(titleRowText);
        titleRowCell.setCellStyle(cellHeaderStyle);
        //创建第一行表头
        XSSFRow headRow = sheet.createRow(2);

        int headerLength = header.length;
        //遍历添加表头
        for (int i = 0; i < headerLength; i++) {
            //创建一个单元格
            XSSFCell cell = headRow.createCell(i);
            //创建一个内容对象
            XSSFRichTextString text = new XSSFRichTextString(header[i]);
            //将内容对象的文字内容写入到单元格中
            cell.setCellValue(text);
            cell.setCellStyle(cellHeaderStyle);
        }
        //创建第二行
        XSSFRow headRow1 = sheet.createRow(3);
        //日期
        for (int i = 0; i < allShowDays.size(); i++) {
            String day = allShowDays.get(i);
            int startColumn = headerLength + i * 3;
            //创建一个单元格
            XSSFCell cell = headRow.createCell(startColumn);
            //创建一个内容对象
            XSSFRichTextString text = new XSSFRichTextString(day);
            //将内容对象的文字内容写入到单元格中
            cell.setCellValue(text);
            cell.setCellStyle(cellHeaderStyle);

            //设置品牌名，节目名，素材名
            XSSFCell cell1 = headRow1.createCell(startColumn);
            XSSFRichTextString brand = new XSSFRichTextString("品牌名");
            cell1.setCellValue(brand);
            cell1.setCellStyle(cellHeaderStyle2);
            XSSFCell cell2 = headRow1.createCell(startColumn + 1);
            XSSFRichTextString program = new XSSFRichTextString("节目名称");
            cell2.setCellValue(program);
            cell2.setCellStyle(cellHeaderStyle2);
            XSSFCell cell3 = headRow1.createCell(startColumn + 2);
            XSSFRichTextString sucai = new XSSFRichTextString("素材名称");
            cell3.setCellValue(sucai);
            cell3.setCellStyle(cellHeaderStyle2);
        }

        //合并单元格
        int lastColNum = headerLength - 1;
        CellRangeAddress titleRegion = new CellRangeAddress(0, 1, 0, lastColNum);
        sheet.addMergedRegion(titleRegion);

        int lastColNum1 = headerLength + allShowDays.size() * 3 - 1;
        CellRangeAddress titleRegion1 = new CellRangeAddress(0, 1, lastColNum + 1, lastColNum1);
        sheet.addMergedRegion(titleRegion1);

        for (int i = 0; i < headerLength; i++) {
            CellRangeAddress region = new CellRangeAddress(2, 3, i, i);
            sheet.addMergedRegion(region);
        }
        int a = headerLength;
        for (int i = 0; i < allShowDays.size(); i++) {
            int firstCol = a + i * 3;
            int lastCol = a + i * 3 + 2;
            CellRangeAddress region = new CellRangeAddress(2, 2, firstCol, lastCol);
            sheet.addMergedRegion(region);
        }

        //------------------向excel表中装入数据---------------------------------------------
        //从第四行开始装入数据
        Integer rowNum = 3;
        for (DeviceBasicInfoVO device : list) {

            int firstRegionRow = rowNum;
            //查询此设备的节目信息
            List<ProgramPlayListVO> programs = programService.getSimpleCurrentSourcesByDeviceCode(device.getDeviceCode(), null);
            //如果没有节目信息，本行只显示设备信息
            if (CollectionUtils.isEmpty(programs)) {
                rowNum++;
                //建一行
                XSSFRow headrow1 = sheet.createRow(rowNum);

                int columnIndex = 0;

                //建一个单元格 终端组
                XSSFCell cell = headrow1.createCell(columnIndex++);
                XSSFRichTextString text = new XSSFRichTextString(device.getGroupName());
                cell.setCellValue(text);
                //建一个单元格 终端名
                cell = headrow1.createCell(columnIndex++);
                text = new XSSFRichTextString(device.getDeviceName());
                cell.setCellValue(text);
                //建一个单元格 导入门店
                cell = headrow1.createCell(columnIndex++);
                text = new XSSFRichTextString(device.getStoreName());
                cell.setCellValue(text);
                //建一个单元格 设备类型/尺寸
                cell = headrow1.createCell(columnIndex++);
                String type = device.getDeviceType() == null ? "" : device.getDeviceType();
                String screen = device.getScreenSize() == null ? "" : device.getScreenSize();
                text = new XSSFRichTextString(type + "/" + screen);
                cell.setCellValue(text);
                //建一个单元格 分辨率
                cell = headrow1.createCell(columnIndex++);
                text = new XSSFRichTextString(device.getResolvingPower());
                cell.setCellValue(text);
                continue;
            }
            //获取每个节目表格中需要显示的日期
            for (ProgramPlayListVO program : programs) {
                setCrossTime(startTime, endTime, program);
            }
            //筛选出需要展示的节目信息
            List<ProgramPlayListVO> hasProgram = programs.stream().filter(bean -> CollectionUtils.isNotEmpty(bean.getShowDays())).collect(Collectors.toList());
            //没有对应节目信息，则只显示设备信息
            if (CollectionUtils.isEmpty(hasProgram)) {
                rowNum++;
                //建一行
                XSSFRow headrow1 = sheet.createRow(rowNum);

                int columnIndex = 0;

                //建一个单元格 终端组
                XSSFCell cell = headrow1.createCell(columnIndex++);
                XSSFRichTextString text = new XSSFRichTextString(device.getGroupName());
                cell.setCellValue(text);
                //建一个单元格 终端名
                cell = headrow1.createCell(columnIndex++);
                text = new XSSFRichTextString(device.getDeviceName());
                cell.setCellValue(text);
                //建一个单元格 导入门店
                cell = headrow1.createCell(columnIndex++);
                text = new XSSFRichTextString(device.getStoreName());
                cell.setCellValue(text);
                //建一个单元格 设备类型/尺寸
                cell = headrow1.createCell(columnIndex++);
                String type = device.getDeviceType() == null ? "" : device.getDeviceType();
                String screen = device.getScreenSize() == null ? "" : device.getScreenSize();
                text = new XSSFRichTextString(type + "/" + screen);
                cell.setCellValue(text);
                //建一个单元格 分辨率
                cell = headrow1.createCell(columnIndex++);
                text = new XSSFRichTextString(device.getResolvingPower());
                cell.setCellValue(text);
                continue;
            }
            //有对应节目信息
            for (ProgramPlayListVO program : hasProgram) {

                rowNum++;
                //建一行
                XSSFRow headrow1 = sheet.createRow(rowNum);

                int columnIndex = 0;

                //建一个单元格 终端组
                XSSFCell cell = headrow1.createCell(columnIndex++);
                XSSFRichTextString text = new XSSFRichTextString(device.getGroupName());
                cell.setCellValue(text);
                //建一个单元格 终端名
                cell = headrow1.createCell(columnIndex++);
                text = new XSSFRichTextString(device.getDeviceName());
                cell.setCellValue(text);
                //建一个单元格 导入门店
                cell = headrow1.createCell(columnIndex++);
                text = new XSSFRichTextString(device.getStoreName());
                cell.setCellValue(text);
                //建一个单元格 设备类型/尺寸
                cell = headrow1.createCell(columnIndex++);
                String type = device.getDeviceType() == null ? "" : device.getDeviceType();
                String screen = device.getScreenSize() == null ? "" : device.getScreenSize();
                text = new XSSFRichTextString(type + "/" + screen);
                cell.setCellValue(text);
                //建一个单元格 分辨率
                cell = headrow1.createCell(columnIndex++);
                text = new XSSFRichTextString(device.getResolvingPower());
                cell.setCellValue(text);

                List<String> showDays = program.getShowDays();
                String brandName = program.getBrandName();
                String programName = program.getProgramName();
                String fileName = program.getFileName();
                for (String day : showDays) {
                    Integer index = dayMap.get(day);
                    int columnStart = columnIndex + 3 * index;
                    //品牌名称
                    cell = headrow1.createCell(columnStart);
                    text = new XSSFRichTextString(brandName);
                    cell.setCellValue(text);
                    //节目名称
                    cell = headrow1.createCell(columnStart + 1);
                    text = new XSSFRichTextString(programName);
                    cell.setCellValue(text);
                    //素材名称
                    cell = headrow1.createCell(columnStart + 2);
                    text = new XSSFRichTextString(fileName);
                    cell.setCellValue(text);
                }
            }
            //合并设备列
            int lastRegionRow = rowNum;
            for (int i = 0; i < headerLength; i++) {
                CellRangeAddress region = new CellRangeAddress(firstRegionRow + 1, lastRegionRow, i, i);
                sheet.addMergedRegion(region);
            }
        }
        return workbook;
    }
    /**
     * 获取节目的时间交集
     *
     * @param startTime 用户所选的开始时间
     * @param endTime   用户选的结束时间
     * @param program   节目信息
     */
    private void setCrossTime(long startTime, long endTime, ProgramPlayListVO program) {
        long resStart;
        long resEnd;
        Long putinStart = program.getPutinStart();
        Long putinEnd = program.getPutinEnd();
        if (putinEnd == null || putinStart == null) {
            return;
        }
        if (endTime < startTime || putinEnd < putinStart) {
            return;
        }
        if (putinStart <= startTime && putinEnd >= startTime && putinEnd <= endTime) {
            resStart = startTime;
            resEnd = putinEnd;
        } else if (putinStart <= startTime && putinEnd >= endTime) {
            resStart = startTime;
            resEnd = endTime;
        } else if (putinStart >= startTime && putinEnd <= endTime) {
            resStart = putinStart;
            resEnd = putinEnd;
        } else if (putinStart >= startTime && putinStart <= endTime && putinEnd >= endTime) {
            resStart = putinStart;
            resEnd = endTime;
        } else {
            return;
        }

        List<String> days = getFormatDaysList(resStart, resEnd);
        program.setShowDays(days);

    }

    /**
     * 获取格式化后的日期集合
     *
     * @param resStart 開始時間
     * @param resEnd   結束時間
     * @return 格式化後的日期集合
     */
    private static List<String> getFormatDaysList(long resStart, long resEnd) {

        List<String> allDays = new ArrayList<>();
        if (resEnd <= resStart) {
            return allDays;
        }
        try {
            //开始时间
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String startDate = sdf.format(new Date(Long.parseLong(String.valueOf(resStart))));
            //结束时间
            String endDate = sdf.format(new Date(Long.parseLong(String.valueOf(resEnd))));

            allDays.add(startDate);
            boolean hasNext = true;
            if (startDate.equals(endDate)) {
                hasNext = false;
            }

            while (hasNext) {
                Date start = sdf.parse(startDate);
                String nextDay = getNextDay(sdf, start);
                allDays.add(nextDay);
                if (nextDay.equals(endDate)) {
                    hasNext = false;
                }
                startDate = nextDay;
            }
        } catch (ParseException e) {
            e.printStackTrace();

        }
        return allDays;
    }

    /**
     * 获取下一天
     *
     * @param sdf   格式
     * @param start 今天的日期
     * @return
     */
    private static String getNextDay(SimpleDateFormat sdf, Date start) {
        Calendar calendar = new GregorianCalendar();
        calendar.setTime(start);
        //把日期往后增加一天,整数  往后推,负数往前移动
        calendar.add(Calendar.DATE, 1);
        Date time = calendar.getTime();
        long nextDayTime = time.getTime();
        String nextDay = sdf.format(new Date(Long.parseLong(String.valueOf(nextDayTime))));
        return nextDay;
    }

}
